<?php
/////////////////////////////////////////////////
// This function use for migrate raw data to temp data
/////////////////////////////////////////////////
function VoteCanvasser_migrate_canvasser(){
  VoteCanvasser_migrate_canvassertype();
}

function VoteCanvasser_migrate_data(){
  VoteCanvasser_migrate_house();
}


function VoteCanvasser_migrate_canvassertype(){
  //Initial value
  //$province_id = 96;
  /////////////////////////////////////////////////////////////////////
  //Migrate sex
  //Select sex from using data & update raw data to temp data
  ///////////////////////////////////////////////////////////////////
  $pro_id = FormUtil::getPassedValue ('pro_id', false , 'GET');
  if($pro_id){
    echo "Start migrate the canvassertype table.<br>";
    //Selection
    $query = "SELECT
                canvassertype.id,
                canvassertype.name 
              FROM 
                rawdata.canvassertype ";

    $column = array(
                      "id",
                      "name"
              );

    $result = DBUtil::executeSQL($query);
    $canvassertypeArray = DBUtil::marshallObjects ($result, $column);
    unset($result);

    foreach($canvassertypeArray as $item){
      //Update
      $where = "canvasser.type = '".trim($item["name"])."'";
      $query = "UPDATE
                  rawdata.canvasser
                SET
                  canvasser.type = ".$item["id"]."
                WHERE ".
                  $where;
        
      $result = DBUtil::executeSQL($query);
      unset($result);
    }
    unset($canvassertypeArray);
  }else{
    echo "You not set the province id(pro_id)."; exit;
  }

  echo "<META HTTP-EQUIV=\"Refresh\" CONTENT=\"3;URL=http://".$_SERVER['SERVER_NAME'].$_SERVER['PHP_SELF']."?module=VoteCanvasser&type=migrate&func=canvassersex&pro_id=".$pro_id."\">";
  pnShutDown();
}


/////////////////////////////////////////////////
// This function use for migrate raw data to temp data
/////////////////////////////////////////////////
function VoteCanvasser_migrate_canvassersex(){
  //Initial value
  //$province_id = 96;
  /////////////////////////////////////////////////////////////////////
  //Migrate sex
  //Select sex from using data & update raw data to temp data
  ///////////////////////////////////////////////////////////////////
  $pro_id = FormUtil::getPassedValue ('pro_id', false , 'GET');
  if($pro_id){
    echo "Start migrate the sex table.<br>";
    //Selection
    $query = "SELECT
                titlename.sex_id,
                titlename.name 
              FROM 
                rawdata.titlename ";

    $column = array(
                      "id",
                      "name"
              );

    $result = DBUtil::executeSQL($query);
    $titlenameArray = DBUtil::marshallObjects ($result, $column);
    unset($result);

    foreach($titlenameArray as $item){
      //Update
      $where = "canvasser.titlename = '".trim($item["name"])."'";
      $query = "UPDATE
                  rawdata.canvasser
                SET
                  canvasser.sex = ".$item["id"]."
                WHERE ".
                  $where;
        
      $result = DBUtil::executeSQL($query);
      unset($result);
    }
    unset($titlenameArray);
  }else{
    echo "You not set the province id(pro_id)."; exit;
  }

  echo "<META HTTP-EQUIV=\"Refresh\" CONTENT=\"3;URL=http://".$_SERVER['SERVER_NAME'].$_SERVER['PHP_SELF']."?module=VoteCanvasser&type=migrate&func=canvasserprovince&pro_id=".$pro_id."\">";
  pnShutDown();
}

function VoteCanvasser_migrate_canvasserprovince(){
  header ('Content-type: text/html; charset=utf-8');


  /////////////////////////////////////////////////////////////////////
  //Migrate province
  //Select province from using data & update raw data to temp data
  ///////////////////////////////////////////////////////////////////

  $pro_id = FormUtil::getPassedValue ('pro_id', false , 'GET');
  if($pro_id){
  echo "Start migrate the province table.<br>";
    //Selection
    $query = "SELECT
                province.id,
                province.name 
              FROM 
                rawdata.province
              WHERE 
                id = ".$pro_id;

    $column = array(
                      "id",
                      "name"
              );
    $result = DBUtil::executeSQL($query);
    $provinceArray = DBUtil::marshallObjects ($result, $column);
    unset($result);

    //Update
    $where = "canvasser.province = '".trim($provinceArray[0]["name"])."'";
    $query = "UPDATE
                rawdata.canvasser
              SET
                canvasser.province = ".$provinceArray[0]["id"]."
              WHERE ".
                $where;
      
    DBUtil::executeSQL($query);
    unset($provinceArray);
  }else{
    echo "You not set the province id(pro_id)."; exit;
  }

  echo "<META HTTP-EQUIV=\"Refresh\" CONTENT=\"3;URL=http://".$_SERVER['SERVER_NAME'].$_SERVER['PHP_SELF']."?module=VoteCanvasser&type=migrate&func=canvasserdistrict&pro_id=".$pro_id."\">";
  pnShutDown();
}

function VoteCanvasser_migrate_canvasserdistrict(){
  header ('Content-type: text/html; charset=utf-8');
  /////////////////////////////////////////////////////////////////////
  //Migrate district
  //Select district from using data & update raw data to temp data
  ///////////////////////////////////////////////////////////////////
  $pro_id = FormUtil::getPassedValue ('pro_id', false , 'GET');
  if($pro_id){
    echo "Start migrate the district table.<br>";
    //Selection
    $query = "SELECT
                district.id,
                district.name 
              FROM 
                rawdata.district
              WHERE 
                district.pro_id = ".$pro_id;

    $column = array(
                      "id",
                      "name"
              );
    $result = DBUtil::executeSQL($query);
    $districtArray = DBUtil::marshallObjects ($result, $column);
    unset($result);

    $i=1;
    foreach($districtArray as $item){
      //Update
      $where = "canvasser.distric = 'อำเภอ".trim($item["name"])."' OR  canvasser.distric = '".trim($item["name"])."'";
      $query = "UPDATE
                  rawdata.canvasser
                SET
                  canvasser.distric = ".$item["id"]."
                WHERE ".
                  $where;

      $result = DBUtil::executeSQL($query);
      if($result){
        echo "District name : ".$item["name"]."<br>";
      }
    }
  }else{
    echo "You not set the province id(pro_id)."; exit;
  }

  echo "<META HTTP-EQUIV=\"Refresh\" CONTENT=\"3;URL=http://".$_SERVER['SERVER_NAME'].$_SERVER['PHP_SELF']."?module=VoteCanvasser&type=migrate&func=canvassertambon&pro_id=".$pro_id."\">";
  pnShutDown();
}

function VoteCanvasser_migrate_canvassertambon(){
  header ('Content-type: text/html; charset=utf-8');
  /////////////////////////////////////////////////////////////////////
  //Migrate district
  //Select district from using data & update raw data to temp data
  ///////////////////////////////////////////////////////////////////
  $pro_id = FormUtil::getPassedValue ('pro_id', false , 'GET');
  if($pro_id){
    echo "Start migrate the tambon table.<br>";
    //Selection
    $query = "SELECT
                tambon.id,
                tambon.dis_id,
                tambon.name 
              FROM 
                rawdata.tambon
              WHERE 
                tambon.pro_id = ".$pro_id;

    $column = array(
                      "id",
                      "dis_id",
                      "name"
              );
    $result = DBUtil::executeSQL($query);
    $districtArray = DBUtil::marshallObjects ($result, $column);
    unset($result);

    $i=1;
    foreach($districtArray as $item){
      //Update
      $where = "canvasser.tumbol LIKE '%".trim($item["name"])."%' AND canvasser.distric = ".$item["dis_id"]." ";
      $query = "UPDATE
                  rawdata.canvasser
                SET
                  canvasser.tumbol = ".$item["id"]."
                WHERE ".
                  $where;
      echo $query."<br>";
      $result = DBUtil::executeSQL($query);
      if($result){
        echo "Tambon name : ".$item["name"]."<br>";
      }
    }
  }else{
    echo "You not set the province id(pro_id)."; exit;
  }
  //exit;
  echo "<META HTTP-EQUIV=\"Refresh\" CONTENT=\"3;URL=http://".$_SERVER['SERVER_NAME'].$_SERVER['PHP_SELF']."?module=VoteCanvasser&type=migrate&func=canvasservillage&pro_id=".$pro_id."\">";

  pnShutDown();


}


function VoteCanvasser_migrate_canvasservillage(){
  header ('Content-type: text/html; charset=utf-8');
  /////////////////////////////////////////////////////////////////////
  //Migrate district
  //Select district from using data & update raw data to temp data
  ///////////////////////////////////////////////////////////////////
  $pro_id = FormUtil::getPassedValue ('pro_id', false , 'GET');
  if($pro_id){
    echo "Start migrate the village table.<br>";
    //Select tumbol
    $query = "SELECT DISTINCT
                canvasser.tumbol 
              FROM 
                rawdata.canvasser
              WHERE 
                canvasser.province = ".$pro_id;

    $column = array(
                      "tumbol"
              );
    $result = DBUtil::executeSQL($query);
    $tambonArray = DBUtil::marshallObjects ($result, $column);
    unset($result);

  foreach($tambonArray as $item){

    //Select moo
    $query = "SELECT DISTINCT
                canvasser.moo 
              FROM 
                rawdata.canvasser
              WHERE 
                canvasser.tumbol = ".$item["tumbol"];

    $column = array(
                      "moo"
              );
    $result = DBUtil::executeSQL($query);
    $mooArray = DBUtil::marshallObjects ($result, $column);
    unset($result);


    foreach($mooArray as $mooItem){
      if($mooItem['moo'] == 0){

        $emptyVillage = true;
        //var_dump($mooItem['moo']); exit;
        //Select moo
        $query = "SELECT 
                    village.id,
                    village.name
                  FROM 
                    rawdata.village
                  WHERE 
                    village.tam_id = ".$item["tumbol"];
        //echo("Moo loop :".$i.$query); 
        $column = array(
                          "id",
                          "name"
                  );
        $result = DBUtil::executeSQL($query);
        $villageArray = DBUtil::marshallObjects ($result, $column);
        unset($result);

        echo "--- Update Village : 00 ---<br>";
        $round = 1;
        foreach($villageArray as $villageItem){

          //Update valiage by road
          $roadName = explode('ถ.', $villageItem["name"], 2);
          $soiName  = explode('ซอย', $villageItem["name"], 2);
          $trokName = explode('ตรอก', $villageItem["name"], 2);

          if($roadName[1]){
            $where = "canvasser.road = '".$roadName[1]."' AND canvasser.tumbol = ".$item["tumbol"];
            $query = "UPDATE
                        rawdata.canvasser
                      SET
                        canvasser.village = ".$villageItem["id"]."
                      WHERE ".
                        $where;
              
            $result = DBUtil::executeSQL($query);
            echo("- - - - - - - ถนน - - - - - - - <br>");
            echo("Magrate : ".$villageItem["name"]." to ".$roadName[1]."<br>");
            echo($query."<br><br>");
          }else if($soiName[1] && !isset($roadName[1])){
            $where = "canvasser.soi = '".$soiName[1]."' AND (canvasser.road IS NULL OR canvasser.road ='')  AND canvasser.tumbol = ".$item["tumbol"];
            $query = "UPDATE
                        rawdata.canvasser
                      SET
                        canvasser.village = ".$villageItem["id"]."
                      WHERE ".
                        $where;
            $result = DBUtil::executeSQL($query);
            echo("- - - - - - - - ซอย - - - - - - <br>");
            echo("Magrate : ".$villageItem["name"]." to ".$soiName[1]."<br>");
            echo($query."<br><br>");
          }else if($trokName[1] && !isset($roadName[1]) && !isset($soiName[1])){
            $where = "canvasser.trok = '".$trokName[1]."' AND (canvasser.road IS NULL OR canvasser.road ='') AND (canvasser.soi IS NULL OR canvasser.soi ='') AND canvasser.tumbol = ".$item["tumbol"];
            $query = "UPDATE
                        rawdata.canvasser
                      SET
                        canvasser.village = ".$villageItem["id"]."
                      WHERE ".
                        $where;
            $result = DBUtil::executeSQL($query);
            echo("- - - - - - - - ตรอก - - - - - - <br>");
            echo("Magrate : ".$villageItem["name"]." to ".$trokName[1]."<br>");
            echo($query."<br><br>");
          }else{
            $where = " canvasser.tumbol = ".$item["tumbol"];
            $query = "UPDATE
                        rawdata.canvasser
                      SET
                        canvasser.village = ".$villageItem["id"]."
                      WHERE ".
                        $where;
            $result = DBUtil::executeSQL($query);
            echo("- - - - - - - - หมู่บ้าน - - - - - - <br>");
            echo("Magrate : ".$villageItem["name"]." to ".$trokName[1]."<br>");
            echo($query."<br><br>");
          }
        }

        $where = "(canvasser.trok IS NULL OR canvasser.trok ='') AND (canvasser.road IS NULL OR canvasser.road ='') AND (canvasser.soi IS NULL OR canvasser.soi ='') AND canvasser.tumbol = ".$item["tumbol"];
        $query = "UPDATE
                    rawdata.canvasser
                  SET
                    canvasser.village = ".$item["tumbol"]."01"."
                  WHERE ".
                    $where;
        $result = DBUtil::executeSQL($query);
        echo("- - - - - - - - ไม่มีที่อยู่ - - - - - - <br>");
        echo("Magrate : 00 to 01 <br>");
        echo($query."<br><br>");


      }else{
          //Update village
          $where = "canvasser.tumbol = '".$item["tumbol"]."' AND canvasser.moo = '".$mooItem["moo"]."'";
          $query = "UPDATE
                      rawdata.canvasser
                    SET
                      canvasser.village = ".$item["tumbol"].sprintf("%02d",$mooItem['moo'])."
                    WHERE ".
                      $where;

          $result = DBUtil::executeSQL($query);
          echo "Village : ".$item["tumbol"].sprintf("%02d",$mooItem['moo'])."<br>";
        }//End check moo == 0
      }//End loop mooArray
    }//End loop tambonArray
  }else{
    echo "You not set the province id(pro_id)."; exit;
  }

  echo "The migration is finished.";
  pnShutDown();
}


/*
*This file is contain function to convert datas from temp database to VoteCanvasser format.
*
*/

function VoteCanvasser_migrate_house(){
  header ('Content-type: text/html; charset=utf-8');
  $pntables = pnDBGetTables();
  $tableHouseNo = $pntables['votecanvasser_house_no'];
  //$columnHouseNo  = $pntables['votecanvasser_house_no_column'];
  //var_dump($tableHouseNo);
  //exit;
  $table = FormUtil::getPassedValue ('table', false , 'GET');
  
  $query = "SELECT DISTINCT house_no,moo,village,tumbol,distric,province,trok,soi,road FROM rawdata.canvasser";

  $column = array(  "no",
                    "vil_moo",
                    "vil_id",
                    "tam_id",
                    "dis_id",
                    "pro_id",
                    "trok",
                    "soi",
                    "road"
                    );
  $result = DBUtil::executeSQL($query);
  $objectArray = DBUtil::marshallObjects ($result, $column);
  $i=0;
  if(!FormUtil::getPassedValue ('offset', false , 'GET')){
    foreach($objectArray AS $object){
      
        $object2[$i] = $object;
        preg_match('/(?P<house_no>\w+)\/(?P<sub_house_no>\d+)/',$object['no'],$matches);

        
        $object2[$i]['sub_house_no'] = $matches['sub_house_no'] ? $matches['sub_house_no'] : "0";
        $object2[$i]['house_no'] = $matches['house_no'] ? $matches['house_no'] : $object['no'];

      $i++;
    }
    
    $object2 = array_msort($object2,array('vil_id'=>SORT_ASC, 'vil_moo'=>SORT_ASC, 'house_no'=>SORT_ASC, 'sub_house_no'=>SORT_ASC));
    
    DBUtil::insertObjectArray($object2, 'votecanvasser_house_no', true);
    unset($object);
    unset($object2);
  }
  
  
  $step=300;
  $startoffset = FormUtil::getPassedValue ('offset', null , 'GET') ? FormUtil::getPassedValue ('offset', null , 'GET') : 0;
  $endoffset = (($startoffset+$step)>count($objectArray)) ? count($objectArray) : ($startoffset+$step);
  
  echo "Table : ".$table."<br>";
  echo "Step :".$step."<br>";
  echo "Start at ".$startoffset."<br>";
  echo "End at ".$endoffset."<hr>";
  echo "Complete : ".$endoffset." (".number_format((($endoffset/count($objectArray)) * 100), 0)."%)<br>";
  echo "Remain : ".(count($objectArray)-$endoffset)."<hr>";
  

  for($i=$startoffset;$i<$endoffset;$i++){
    $value = $objectArray[$i];
    $query = "SELECT
                count(*)
              FROM
                rawdata.canvasser".$table."
              WHERE
                house_no='".$value['no']."'
              AND
                moo='".$value['vil_moo']."'
			  AND
                village='".$value['vil_id']."'";
   
    $result = DBUtil::executeSQL($query);
    $objectArray2 = DBUtil::marshallObjects ($result, array('num'));
    echo "[".$i."] House no : ".$value['no']."(".$value['vil_moo'].") - ".$objectArray2[0]['num']."person - ";
    $query = "UPDATE
                $tableHouseNo
              SET
                hou_amount_person=".$objectArray2[0]['num']."
              WHERE
                hou_no='".$value['no']."'
              AND
                hou_vil_moo='".$value['vil_moo']."'
			  AND
                hou_vil_id='".$value['vil_id']."'";
   
    $result = DBUtil::executeSQL($query);
    if($result){
      echo "Successed!!<br />\n";
    }else{
      echo "Failed!!<br />\n";
    }
    unset($objectArray2);
  }

  if($endoffset>=count($objectArray)){
    echo "Complete : ".$endoffset."/".count($objectArray);
    echo "<META HTTP-EQUIV=\"Refresh\" CONTENT=\"3;URL=http://".$_SERVER['SERVER_NAME'].$_SERVER['PHP_SELF']."?module=VoteCanvasser&type=migrate&func=person\">";
    unset($objectArray);
    unset($result);
    exit;
  }else{
    echo "Complete : ".$endoffset."/".count($objectArray);
    echo "<META HTTP-EQUIV=\"Refresh\" CONTENT=\"3;URL=http://".$_SERVER['SERVER_NAME'].$_SERVER['PHP_SELF']."?module=VoteCanvasser&type=migrate&func=house&offset=".$endoffset."\">";
    //echo "<hr><a href='index.php?module=VoteCanvasser&type=migrate&func=housetable1&offset=".$endoffset."'>Next</a>";
    unset($objectArray);
    unset($result);
    exit;
  }
}


function VoteCanvasser_migrate_person(){
  header ('Content-type: text/html; charset=utf-8');
  $pntables = pnDBGetTables();
  $tablePerson = $pntables['votecanvasser_person'];
  $columnPerson  = $pntables['votecanvasser_person_column'];
  $tableHouseNo = $pntables['votecanvasser_house_no'];
  $columnHouseNo  = $pntables['votecanvasser_house_no_column'];

  $table = FormUtil::getPassedValue ('table', false , 'GET');
  $startoffset = FormUtil::getPassedValue ('offset', false , 'GET');
  
  $query = "SELECT house_no,moo,village,tumbol,distric,province,trok,soi,road,first_name,last_name,sex,titlename,telephone,type FROM rawdata.canvasser";
  

  $column = array(  "no",
                    "vil_moo",
                    "vil_id",
                    "tam_id",
                    "dis_id",
                    "pro_id",
                    "trok",
                    "soi",
                    "road",
                    "firstname",
                    "lastname",
                    "gen_id",
                    "titlename",
                    "telephone",
                    "type"
                    );
  $result = DBUtil::executeSQL($query);
  $objectArray = DBUtil::marshallObjects ($result, $column);
  if(!FormUtil::getPassedValue ('offset', false , 'GET')){
    DBUtil::insertObjectArray($objectArray, 'votecanvasser_person', true);
  }

 
  $step=300;
  $startoffset = FormUtil::getPassedValue ('offset', null , 'GET') ? FormUtil::getPassedValue ('offset', null , 'GET') : 0;
  $endoffset = (($startoffset+$step)>count($objectArray)) ? count($objectArray) : ($startoffset+$step);
  echo "Table : ".$table."<br>";
  echo "Step :".$step."<br>";
  echo "Start at ".$startoffset."<br>";
  echo "End at ".$endoffset."<br>";
  echo "Complete : ".$endoffset." (".number_format((($endoffset/count($objectArray)) * 100), 0)."%)<br>";
  echo "Remain : ".(count($objectArray)-$endoffset)."<hr>";

  for($i=$startoffset;$i<$endoffset;$i++){
    $value = $objectArray[$i];
    $query = "SELECT
                hou_id
              FROM
                $tableHouseNo
              WHERE
                hou_no='".$value['no']."'
              AND
                hou_vil_moo='".$value['vil_moo']."'
              AND
                hou_vil_id='".$value['vil_id']."'";
    $result = DBUtil::executeSQL($query);
    $objectHouse = DBUtil::marshallObjects ($result, array('hou_id'));
    
    echo "[".$i."] Person : ".$value['firstname']." ".$value['lastname']." - House ID : ".$objectHouse[0]['hou_id']."<br />\n";
    
    $query = "UPDATE
                $tablePerson
              SET
                per_hou_id=".$objectHouse[0]['hou_id']."
              WHERE
                per_firstname='".$value['firstname']."'
              AND
                per_lastname='".$value['lastname']."'
              AND
                per_vil_id='".$value['vil_id']."'";

    
    $result = DBUtil::executeSQL($query);
    unset($objectHouse);
    unset($result);
    unset($value);
  }
  if($endoffset>=count($objectArray))
  {
    echo "Complete : ".$endoffset."/".count($objectArray);
    unset($objectArray);
    unset($result);
    exit;

  }else{

    echo "<META HTTP-EQUIV=\"Refresh\" CONTENT=\"3;URL=http://".$_SERVER['SERVER_NAME'].$_SERVER['PHP_SELF']."?module=VoteCanvasser&type=migrate&func=person&offset=".$endoffset."\">";
    //echo "<hr><a href='index.php?module=VoteCanvasser&type=migrate&func=housetable1&offset=".$endoffset."'>Next</a>";
    unset($objectArray);
    unset($result);
    exit;
  }
}

function getHouseId()
{
    foreach($objectArray as $id=>$array){
    if(($array['no']=="108")&&($array['vil_moo']==1))
    $value[] = $id;
  }
}


function array_msort($array, $cols)
{
    $colarr = array();
    foreach ($cols as $col => $order) {
        $colarr[$col] = array();
        foreach ($array as $k => $row) { $colarr[$col]['_'.$k] = strtolower($row[$col]); }
    }
    $params = array();
    foreach ($cols as $col => $order) {
        $params[] =& $colarr[$col];
        $params = array_merge($params, (array)$order);
    }
    call_user_func_array('array_multisort', $params);
    $ret = array();
    $keys = array();
    $first = true;
    foreach ($colarr as $col => $arr) {
        foreach ($arr as $k => $v) {
            if ($first) { $keys[$k] = substr($k,1); }
            $k = $keys[$k];
            if (!isset($ret[$k])) $ret[$k] = $array[$k];
            $ret[$k][$col] = $array[$k][$col];
        }
        $first = false;
    }
    return $ret;

}
?>